﻿/****************************** Module Header ******************************\
 * Module Name:  ExcelHelper.cs
 * Module Version:  1.0.0 (2012-11-27)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to encrypt string or encrypt number.
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace UCF.Core.Data
{
    public class ExcelHelper
    {
        private string filename;
        public ExcelHelper(string filename)
        {
            this.filename = filename;
        }
        public OleDbConnection GetConnection()
        {
            return new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended Properties=\"Excel 8.0;HDR=YES\"", filename));
        }

        // !c@2012-12-23
        public DataSet ExecuteDataSet(string sheetName = "Sheet1", bool firstRowIsTitle = true)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties='Excel 8.0;HDR=" + ((firstRowIsTitle) ? "Yes" : "No") + ";IMEX=1'";
            using (OleDbConnection OleConn = new OleDbConnection(strConn))
            {
                OleConn.Open();
                String sql = "SELECT * FROM  [" + sheetName + "$]";//可是更改Sheet名称，比如sheet2，等等    

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, sheetName);
                return OleDsExcle;
            }
        }

        public int ExecuteNonQuery(string sql)
        {
            using (OleDbConnection conn = GetConnection())
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = sql;
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        private void CheckColumn(DataTable dt, DataTable dt_v)
        {
            foreach (DataRow dr in dt_v.Select())
            {
                if (!dt.Columns.Contains(dr["列名"].ToString()))
                {
                    dr.Delete();
                }
            }
            dt_v.AcceptChanges();
        }

        private string GetDataType(Type i)
        {
            string s;
            switch (i.Name)
            {
                case "String":
                    s = "Char";
                    break;
                case "Int32":
                    s = "Int";
                    break;
                case "Int64":
                    s = "Int";
                    break;
                case "Int16":
                    s = "Int";
                    break;
                case "Double":
                    s = "Double";
                    break;
                case "Decimal":
                    s = "Double";
                    break;
                default:
                    s = "Char";
                    break;
            }
            return s;
        }

        private OleDbType StringToOleDbType(Type i)
        {
            OleDbType s;

            switch (i.Name)
            {
                case "String":
                    s = OleDbType.Char;
                    break;
                case "Int32":
                    s = OleDbType.Integer;
                    break;
                case "Int64":
                    s = OleDbType.Integer;
                    break;
                case "Int16":
                    s = OleDbType.Integer;
                    break;
                case "Double":
                    s = OleDbType.Double;
                    break;
                case "Decimal":
                    s = OleDbType.Decimal;
                    break;
                default:
                    s = OleDbType.Char;
                    break;
            }
            return s;
        }

        public string GetCreateTableSql(DataTable dt, string sheetName)
        {
            if (dt.Columns.Count == 0) return string.Empty;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("CREATE TABLE {0} (", sheetName);
            string typename;
            foreach (DataColumn c in dt.Columns)
            {
                typename=GetDataType(c.DataType);
                if (typename == "Char") typename = "text";
                sb.AppendFormat("[{0}] {1},", c.ColumnName, typename);
            }
            return string.Format("{0})",sb.ToString().TrimEnd(','));
        }


        // 生成 InsertCommand 并设置参数
        public OleDbCommand GetInsertCommand(string sheetName, DataTable dt, OleDbConnection conn_excel)
        {
            StringBuilder sb = new StringBuilder();
            OleDbCommand i;
            sb.AppendFormat("INSERT INTO [{0}$] (", sheetName);
            foreach (DataColumn c in dt.Columns)
            {
                sb.AppendFormat("[{0}],",c.ColumnName);
            }
            sb=sb.Remove(sb.Length - 1, 1);
            sb.AppendFormat(") VALUES (");
            foreach (DataColumn c in dt.Columns)
            {
                sb.Append("?,");
            }
            sb = sb.Remove(sb.Length - 1, 1);
            sb.Append(")");

            i = new OleDbCommand(sb.ToString(), conn_excel);
            foreach (DataColumn c in dt.Columns)
            {
                i.Parameters.Add("@" + c.Caption, StringToOleDbType(c.DataType), 0, c.Caption);
            }
            return i;
        }
    }

}
